﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class ProgressPrePurchaseRequestDAL : IDALProgressPrePurchaseRequest
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ProgressPrePurchaseRequestDAL()
        {

        }

        public IList<PrePurchaseRequestRouteInfo> GetPrePurchaseRequestApprove(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestRouteInfo> InfoList = new List<PrePurchaseRequestRouteInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_ppr_seq", seq);

                strSQL = "select r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", rs.route_status_name" +
                            ", r.remark" +
                        " from icm_pre_purchase_request_route r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                        " where r.route_type = 'APPROVE' AND r.icm_ppr_seq = @icm_ppr_seq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestRouteInfo info = new PrePurchaseRequestRouteInfo();
                        HrmEmployeeInfo InfoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();
                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));
                        InfoFromEmp.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoFromEmp;
                        infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;
                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;
                        info.Remark = (dr[i]["remark"]).ToString();
                        InfoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<PrePurchaseRequestRouteInfo> GetPrePurchaseRequestInform(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestRouteInfo> InfoList = new List<PrePurchaseRequestRouteInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_ppr_seq", seq);

                strSQL = "select r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", r.from_emp_seq" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", js.job_site_name" +
                            ", concat(c.CLASS_NAME, ' ตำแหน่ง' ,pos.POS_NAME) as emp_descr" +
                        " from icm_pre_purchase_request_route r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " left join hrm_mp_job_sites js on e2.working_job_site_seq = js.job_site_seq" +
                            " LEFT JOIN HRM_MP_CLASSES c ON e2.CLASS_SEQ = c.CLASS_SEQ" +
                            " LEFT JOIN HRM_MP_POSITIONS pos ON pos.POS_SEQ = e2.POS_SEQ" +
                        " where r.route_type = 'INFORM' AND r.icm_ppr_seq = @icm_ppr_seq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestRouteInfo info = new PrePurchaseRequestRouteInfo();
                        HrmEmployeeInfo InfoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        HrmJobSiteInfo InfoJobSite = new HrmJobSiteInfo();
                        HrmClassInfo InfoClass = new HrmClassInfo();

                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));
                        
                        InfoFromEmp.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoFromEmp;
                        
                        infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;
                        
                        InfoJobSite.Name = (dr[i]["job_site_name"]).ToString();
                        info.InfoToEmp.InfoJobSite = InfoJobSite;

                        InfoClass.Name = (dr[i]["emp_descr"]).ToString();
                        info.InfoToEmp.InfoClass = InfoClass;

                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<PrePurchaseRequestRouteHistoryInfo> GetPrePurchaseRequestApproveHistory(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestRouteHistoryInfo> InfoList = new List<PrePurchaseRequestRouteHistoryInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_ppr_seq", seq);

                strSQL = "select r.revision_no " +
                            ", r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", rs.route_status_name" +
                            ", r.remark" +
                        " from icm_pre_purchase_request_route_history r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                            " where r.route_type = 'APPROVE' and r.icm_ppr_seq = @icm_ppr_seq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestRouteHistoryInfo info = new PrePurchaseRequestRouteHistoryInfo();
                        HrmEmployeeInfo InfoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();
                        info.RevisionNo = (Convert.IsDBNull(dr[i]["revision_no"]) ? null : (Nullable<int>)(dr[i]["revision_no"]));

                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));

                        InfoFromEmp.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoFromEmp;

                        infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;

                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;

                        info.Remark = (dr[i]["remark"]).ToString();
                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<PrePurchaseRequestRouteHistoryInfo> GetPrePurchaseRequestInformHistory(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestRouteHistoryInfo> InfoList = new List<PrePurchaseRequestRouteHistoryInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_ppr_seq", seq);

                strSQL = "select r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", r.from_emp_seq" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", js.job_site_name" +
                            ", concat(c.CLASS_NAME, ' ตำแหน่ง' ,pos.POS_NAME) as emp_descr" +
                        " from icm_pre_purchase_request_route_history r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " left join hrm_mp_job_sites js on e2.working_job_site_seq = js.job_site_seq" +
                            " LEFT JOIN HRM_MP_CLASSES c ON e2.CLASS_SEQ = c.CLASS_SEQ" +
                            " LEFT JOIN HRM_MP_POSITIONS pos ON pos.POS_SEQ = e2.POS_SEQ" +
                        " where r.route_type = 'INFORM' AND r.icm_ppr_seq = @icm_ppr_seq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestRouteHistoryInfo info = new PrePurchaseRequestRouteHistoryInfo();
                        HrmEmployeeInfo InfoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        HrmJobSiteInfo InfoJobSite = new HrmJobSiteInfo();
                        HrmClassInfo InfoClass = new HrmClassInfo();

                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));

                        InfoFromEmp.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoFromEmp;

                        infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;

                        InfoJobSite.Name = (dr[i]["job_site_name"]).ToString();
                        info.InfoToEmp.InfoJobSite = InfoJobSite;

                        InfoClass.Name = (dr[i]["emp_descr"]).ToString();
                        info.InfoToEmp.InfoClass = InfoClass;

                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<PrePurchaseRequestInfo> GetPrePurchaseRequest(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> InfoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();

                strSQL = "select r.icm_pr_seq,r.purchase_date " +
                            ",  concat(p.prefix_name, e.first_name, ' ' ,e.last_name) as request_emp" +
                            ", r.icm_pr_ref_no" +
                            ", r.icm_pr_name" +
                            ", s.record_status_name" +
                        " from icm_purchase_request r" +
                            " left join hrm_ps_employees e on r.purchase_emp_seq = e.emp_seq" +
                            " left join mas_name_prefixes p on e.name_prefix_seq = p.name_prefix_seq" +
                            " inner join sys_record_status s on r.record_status = s.record_status";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        HrmEmployeeInfo InfoEmpReq = new HrmEmployeeInfo();
                        SysRecordStatusInfo infoRecordStstus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32((dr[i]["icm_pr_seq"]).ToString());
                        info.DateRequired = (Convert.IsDBNull(dr[i]["purchase_date"]) ? null : (Nullable<DateTime>)(dr[i]["purchase_date"]));

                        InfoEmpReq.Name = (dr[i]["request_emp"]).ToString();
                        info.InfoEmployeeRequest = InfoEmpReq;

                        info.Ref_no = (dr[i]["icm_pr_ref_no"]).ToString();
                        info.Name = (dr[i]["icm_pr_name"]).ToString();

                        infoRecordStstus.Name = (dr[i]["record_status_name"]).ToString();
                        info.InfoRecordStatus = infoRecordStstus;
                        
                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<PrePurchaseRequestRouteInfo> GetPrePurchaseRequestRoute(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestRouteInfo> InfoList = new List<PrePurchaseRequestRouteInfo>();
            try
            {
                dbManager.Open();

                strSQL = "select r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", rs.route_status_name" +
                            ", r.remark" +
                        " from icm_pre_purchase_request_route r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                        " where r.route_type = 'APPROVE'";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestRouteInfo info = new PrePurchaseRequestRouteInfo();
                        HrmEmployeeInfo InfoEmpFrom = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();
                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));
                        InfoEmpFrom.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoEmpFrom;
                        infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;
                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;
                        info.Remark = (dr[i]["remark"]).ToString();
                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<BidInfo> GetBid(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidInfo> InfoList = new List<BidInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_ppr_seq", seq);

                //strSQL = "SELECT *"
                //    + " FROM ICM_BID b"
                //        + " LEFT JOIN ICM_PURCHASE_REQUEST pr ON b.ICM_PR_SEQ = pr.ICM_PR_SEQ";

                strSQL = "select b.icm_bid_seq,b.bid_date" +
                            ",  concat(p.prefix_name, e.first_name, ' ' ,e.last_name) as bid_emp" +
                            ", b.icm_bid_ref_no" +
                            ", b.icm_bid_name" +
                            ", s.record_status_name" +
                        " from icm_bid b" +
                            " left join hrm_ps_employees e on b.bid_emp_seq = e.emp_seq" +
                            " left join mas_name_prefixes p on e.name_prefix_seq = p.name_prefix_seq" +
                            " inner join sys_record_status s on b.record_status = s.record_status";
                                        ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidInfo info = new BidInfo();
                        HrmEmployeeInfo InfoEmp = new HrmEmployeeInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                        info.Seq = Convert.ToInt32((dr[i]["icm_bid_seq"]).ToString());
                        info.DateBid = (Convert.IsDBNull(dr[i]["bid_date"]) ? null : (Nullable<DateTime>)(dr[i]["bid_date"]));

                        InfoEmp.Name = (dr[i]["bid_emp"]).ToString();
                        info.InfoEmployeeBid = InfoEmp;

                        info.Ref_no = (dr[i]["icm_bid_ref_no"]).ToString();
                        info.Name = (dr[i]["icm_bid_name"]).ToString();

                        infoRecordStatus.Name = (dr[i]["record_status_name"]).ToString();
                        infoRecord.InfoStatus = infoRecordStatus;
                        info.InfoRecord = infoRecord;

                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<BidRouteInfo> GetBidRoute(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidRouteInfo> InfoList = new List<BidRouteInfo>();
            try
            {
                dbManager.Open();

                strSQL = "select r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", rs.route_status_name" +
                            ", r.remark" +
                        " from icm_bid_route r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                        " where r.route_type = 'APPROVE'";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidRouteInfo info = new BidRouteInfo();
                        HrmEmployeeInfo InfoEmpHeard = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmpTo = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();
                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));
                        InfoEmpHeard.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoEmpHeard;
                        infoEmpTo.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoEmpTo;
                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;
                        info.Remark = (dr[i]["remark"]).ToString();
                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<PurchaseOrderInfo> GetPurchaseOrder(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInfo> InfoList = new List<PurchaseOrderInfo>();
            try
            {
                dbManager.Open();

                strSQL = "SELECT o.icm_po_seq," +
                            "o.order_date," +
                            "concat(p.prefix_name,e.first_name,' ' ,e.last_name) AS bid_emp," +
                            "o.icm_po_ref_no," +
                            "o.icm_po_name," +
                            "s.record_status_name" +
                        " from icm_purchase_order o" +
                            " left join hrm_ps_employees e on o.order_emp_seq = e.emp_seq" +
                            " left join mas_name_prefixes p on e.name_prefix_seq = p.name_prefix_seq" +
                            " inner join sys_record_status s on o.record_status = s.record_status";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInfo info = new PurchaseOrderInfo();
                        HrmEmployeeInfo InfoEmp = new HrmEmployeeInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                        info.Seq = Convert.ToInt32((dr[i]["icm_po_seq"]).ToString());
                        info.Date = (Convert.IsDBNull(dr[i]["order_date"]) ? null : (Nullable<DateTime>)(dr[i]["order_date"]));

                        InfoEmp.Name = (dr[i]["bid_emp"]).ToString();
                        info.InfoOrderEmp = InfoEmp;

                        info.Ref_No = (dr[i]["icm_po_ref_no"]).ToString();
                        info.Name = (dr[i]["icm_po_name"]).ToString();

                        infoRecordStatus.Name = (dr[i]["record_status_name"]).ToString();
                        info.InfoRecordStatus = infoRecordStatus;

                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<PurchaseOrderRouteInfo> GetPurchaseOrderRoute(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderRouteInfo> InfoList = new List<PurchaseOrderRouteInfo>();
            try
            {
                dbManager.Open();

                strSQL = "select r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", rs.route_status_name" +
                            ", r.remark" +
                        " from icm_bid_route r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                        " where r.route_type = 'APPROVE'";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderRouteInfo info = new PurchaseOrderRouteInfo();
                        HrmEmployeeInfo InfoEmpHeard = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmpTo = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();
                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));
                        InfoEmpHeard.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoEmpHeard;
                        infoEmpTo.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoEmpTo;
                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;
                        info.Remark = (dr[i]["remark"]).ToString();
                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<DeliveryNoteInfo> GetDeliveryNote(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNoteInfo> InfoList = new List<DeliveryNoteInfo>();
            try
            {
                dbManager.Open();

                strSQL = "select d.icm_dn_seq,d.delivery_date" +
                            ",  concat(p.prefix_name, e.first_name, ' ' ,e.last_name) as delivery_emp" +
                            ", d.icm_dn_ref_no" +
                            ", d.icm_dn_name" +
                            ", s.record_status_name" +
                        " from icm_delivery_note d" +
                            " left join hrm_ps_employees e on d.delivery_emp_seq = e.emp_seq" +
                            " left join mas_name_prefixes p on e.name_prefix_seq = p.name_prefix_seq" +
                            " inner join sys_record_status s on d.record_status = s.record_status";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteInfo info = new DeliveryNoteInfo();
                        HrmEmployeeInfo InfoEmp = new HrmEmployeeInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                        info.Seq = Convert.ToInt32((dr[i]["icm_dn_seq"]).ToString());
                        info.DeliveryDate = (Convert.IsDBNull(dr[i]["delivery_date"]) ? null : (Nullable<DateTime>)(dr[i]["delivery_date"]));

                        InfoEmp.Name = (dr[i]["delivery_emp"]).ToString();
                        info.InfoDeliveryEmp = InfoEmp;

                        info.RefNo = (dr[i]["icm_dn_ref_no"]).ToString();
                        info.Name = (dr[i]["icm_dn_name"]).ToString();

                        infoRecordStatus.Name = (dr[i]["record_status_name"]).ToString();
                        info.InfoRecordStatus = infoRecordStatus;

                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }

        public IList<DeliveryNoteRouteInfo> GetDeliveryNoteRoute(int seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNoteRouteInfo> InfoList = new List<DeliveryNoteRouteInfo>();
            try
            {
                dbManager.Open();

                strSQL = "select r.icm_dn_route_seq,r.order_seq" +
                            ",  concat(p1.prefix_name, e1.first_name, ' ' ,e1.last_name) as from_emp" +
                            ", concat(p2.prefix_name, e2.first_name, ' ' ,e2.last_name) as to_emp" +
                            ", rs.route_status_name" +
                            ", r.remark" +
                        " from icm_delivery_note_route r" +
                            " inner join hrm_ps_employees e1 on r.from_emp_seq = e1.emp_seq" +
                            " inner join mas_name_prefixes p1 on e1.name_prefix_seq = p1.name_prefix_seq" +
                            " inner join hrm_ps_employees e2 on r.to_emp_seq = e2.emp_seq" +
                            " inner join mas_name_prefixes p2 on e2.name_prefix_seq = p2.name_prefix_seq" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                            " where r.route_type = 'APPROVE'";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteRouteInfo info = new DeliveryNoteRouteInfo();
                        HrmEmployeeInfo InfoEmpHeard = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmpTo = new HrmEmployeeInfo();
                        SysRouteStatusInfo infoRouteStatus = new SysRouteStatusInfo();

                        info.Seq = Convert.ToInt32((dr[i]["icm_dn_route_seq"]).ToString());
                        info.Order = Convert.ToInt32((dr[i]["order_seq"]).ToString());

                        InfoEmpHeard.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoEmpHeard;

                        infoEmpTo.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoEmpTo;

                        infoRouteStatus.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = infoRouteStatus;

                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            return InfoList;
        }
    }
}
